package com.pine.dbant.common.plugin.meta.function.jdbc;

import com.google.common.collect.Lists;
import com.pine.dbant.common.model.Field;
import com.pine.dbant.common.model.JdbcMetadataTreeView;
import com.pine.dbant.common.model.PrimaryKey;
import com.pine.dbant.common.model.Table;
import com.pine.dbant.common.plugin.meta.JdbcMetaAnalysisException;
import com.pine.dbant.common.plugin.meta.JdbcMetadataAnalysisConfig;
import com.pine.dbant.common.plugin.meta.QueryMetadataFunction;
import com.pine.dbant.common.plugin.meta.QueryMetadataParam;
import com.pine.sunflower.core.utils.Strings;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;

import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author xiaoyuan
 * @create 2023/1/3 11:03
 **/
@Slf4j
public class TableDetailFunction implements QueryMetadataFunction<JdbcMetadataAnalysisConfig, Table> {

    protected static final String TABLE_CAT = "TABLE_CAT";
    protected static final String TABLE_CATALOG = "TABLE_CATALOG";
    protected static final String TABLE_SCHEM = "TABLE_SCHEM";
    protected static final String TABLE_NAME = "TABLE_NAME";
    protected static final String TABLE_TYPE = "TABLE_TYPE";
    protected static final String REMARKS = "REMARKS";
    protected static final String COLUMN_NAME = "COLUMN_NAME";
    protected static final String DATA_TYPE = "DATA_TYPE";
    protected static final String TYPE_NAME = "TYPE_NAME";
    protected static final String COLUMN_SIZE = "COLUMN_SIZE";
    protected static final String BUFFER_LENGTH = "BUFFER_LENGTH";
    protected static final String DECIMAL_DIGITS = "DECIMAL_DIGITS";
    protected static final String NUM_PREC_RADIX = "NUM_PREC_RADIX";
    protected static final String NULLABLE = "NULLABLE";
    protected static final String COLUMN_DEF = "COLUMN_DEF";
    protected static final String SQL_DATA_TYPE = "SQL_DATA_TYPE";
    protected static final String SQL_DATETIME_SUB = "SQL_DATETIME_SUB";
    protected static final String CHAR_OCTET_LENGTH = "CHAR_OCTET_LENGTH";
    protected static final String ORDINAL_POSITION = "ORDINAL_POSITION";
    protected static final String IS_NULLABLE = "IS_NULLABLE";
    protected static final String SCOPE_CATALOG = "SCOPE_CATALOG";
    protected static final String SCOPE_SCHEMA = "SCOPE_SCHEMA";
    protected static final String SCOPE_TABLE = "SCOPE_TABLE";
    protected static final String SOURCE_DATA_TYPE = "SOURCE_DATA_TYPE";
    protected static final String TYPE = "TYPE";
    protected static final String NON_UNIQUE = "NON_UNIQUE";
    protected static final String INDEX_NAME = "INDEX_NAME";
    protected static final String KEY_SEQ = "KEY_SEQ";
    protected static final String PK_NAME = "PK_NAME";
    /**
     * 一般数据库的判断自增的属性名
     */
    protected static final String IS_AUTOINCREMENT = "IS_AUTOINCREMENT";


    @Override
    public Table query(JdbcMetadataAnalysisConfig metadataAnalysisConfig, QueryMetadataParam queryMetadataParam) {
        DatabaseMetaData dbMetaData;
        try {
            dbMetaData = metadataAnalysisConfig.getConnection().getMetaData();
        } catch (SQLException e) {
            throw new JdbcMetaAnalysisException(e.getMessage(), e);
        }
        String catalog = queryMetadataParam.getConfiguration().getNecessaryValue("catalog");
        String tableName = queryMetadataParam.getConfiguration().getNecessaryValue("tableName");
        Table table = buildTableInfo(dbMetaData, catalog, null, tableName);
        if (table == null) {
            throw new JdbcMetaAnalysisException(Strings.format("未发现表信息[{}]", tableName));
        }
        table.setFields(buildFiled(dbMetaData, catalog, null, tableName));
        table.getProperties().put("index", metadataAnalysisConfig
                .getFunction(JdbcMetadataTreeView.TABLE_INDEX.getType())
                .query(metadataAnalysisConfig, queryMetadataParam));
        return table;
    }

    @Override
    public String getType() {
        return JdbcMetadataTreeView.TABLE_DETAIL.getType();
    }

    public Table buildTableInfo(DatabaseMetaData dbMetaData, String catalog, String schemaName, String tableName) {
        List<Table> tables = new ArrayList<>();
        try (ResultSet rs = dbMetaData.getTables(catalog, schemaName, tableName,
                new String[]{"TABLE"})) {
            while (rs.next()) {
//                String tbCatalog = rs.getString(TABLE_CAT);
                String tbSchema = rs.getString(TABLE_SCHEM);
                String tbName = rs.getString(TABLE_NAME);
                // dbMetaData.getTables(catalog, schemaPattern, tablePattern)中：
                // schemaPattern, tablePattern表示模糊匹配
                // Jdk Doc: 一些DatabaseMetaData方法使用String模式的参数。 这些参数都有名称，如fooPattern。
                // 在模式字符串中，“%”表示匹配0个或更多字符的任何子字符串，“_”表示匹配任何一个字符。
                // 只返回与搜索模式匹配的元数据条目。 如果搜索模式参数设置为null ，则该参数的标准将从搜索中删除。
                // 这种情况可能会查出其他schema或者其他表, 需要过滤
                if (schemaName != null && !schemaName.equals(tbSchema)) {
                    continue;
                }
                // 表类型
                String tableType = rs.getString(TABLE_TYPE);
                // 表备注
                String remarks = rs.getString(REMARKS);
                Table table = new Table();
                table.setCatalogName(catalog);
                table.setSchemaName(schemaName);
                table.setTableComment(remarks);
                table.setTableName(tbName.trim());
                table.setTableType(tableType);
                tables.add(table);
            }
        } catch (SQLException e) {
            log.error("获取数据库[{}]->schema[{}]下的表信息错误", catalog, schemaName, e);
            throw new JdbcMetaAnalysisException(String.format("获取数据库[%s]->schema[%s]下的表信息错误", catalog, schemaName), e);
        }
        return CollectionUtils.isNotEmpty(tables) ? tables.get(0) : null;
    }

    protected List<Field> buildFiled(DatabaseMetaData dbMetaData, String catalog, String schemaName, String tableName) {
        List<Field> columns = new ArrayList<>();
        List<PrimaryKey> primaryKeys = getPrimaryKeys(dbMetaData, catalog, schemaName, tableName);
        try (ResultSet rs = dbMetaData.getColumns(catalog, schemaName, tableName, null)) {
            while (rs.next()) {
                // tableName中若带有下划线，例如C_2,则dbMetaData.getColumns会对表名模糊匹配，导致找出其他表如CA2表的字段
                // 需要过滤
                String realTableName = rs.getString(TABLE_NAME);
                if (StringUtils.isNotEmpty(tableName) && !realTableName.equals(tableName)) {
                    continue;
                }
                // 过滤schema中被模糊匹配到其他schema的字段
                String realSchema = rs.getString(TABLE_SCHEM);
                if (StringUtils.isNotBlank(schemaName) && !schemaName.equals(realSchema)) {
                    continue;
                }
                int ordinalPosition = rs.getInt(ORDINAL_POSITION);
                String columnName = rs.getString(COLUMN_NAME);
                //String typeName = rs.getString(TYPE_NAME);
                int jdbcType = rs.getInt(DATA_TYPE);
                String comment = rs.getString(REMARKS);
                String defaultValue = rs.getString(COLUMN_DEF);
                int columnSize = rs.getInt(COLUMN_SIZE);
                int decimalDigits = rs.getInt(DECIMAL_DIGITS);
                boolean autoincrement = isColumnAutoincrement(rs);
                boolean nullable = isColumnNullable(rs);

                Field column = new Field();
                column.setKey(columnName);
                column.setCatalogName(catalog);
                column.setSchemaName(schemaName);
                column.setTableName(tableName);
                column.setOrdinalPosition(ordinalPosition);
                column.setColumnName(columnName);
                column.setJdbcType(jdbcType);
                column.setFieldComment(comment);
                column.setPrimaryKey(primaryKeys.stream().anyMatch(r -> r.getColumnName().trim().equalsIgnoreCase(columnName)));
                column.setDefaultValue(defaultValue);
                column.setSize(columnSize);
                column.setDecimalDigits(decimalDigits);
                column.setAutoincrement(autoincrement);
                column.setNullable(nullable);
                columns.add(column);
            }
        } catch (SQLException e) {
            log.error("获取字段元数据信息错误[catalog:{},schema:{},table:{}]", catalog, schemaName, tableName, e);
            throw new JdbcMetaAnalysisException(e.getMessage(), e);
        }
        return columns;
    }

    public List<PrimaryKey> getPrimaryKeys(DatabaseMetaData dbMetaData, String catalog, String schemaName, String tableName) {
        List<PrimaryKey> primaryKeys = Lists.newArrayList();
        try (ResultSet rs = dbMetaData.getPrimaryKeys(catalog, schemaName, tableName)) {
            while (rs.next()) {
                primaryKeys.add(new PrimaryKey(rs.getString(COLUMN_NAME), rs.getShort(KEY_SEQ), rs.getString(PK_NAME)));
            }
        } catch (SQLException e) {
            log.error("获取数据库[{}]下schema[{}]的表[{}]的主键信息错误", catalog, schemaName, tableName, e);
            throw new JdbcMetaAnalysisException(e.getMessage(), e);
        }
        return primaryKeys;
    }


    /**
     * 判断该列是否允许包含空值
     *
     * @param columnMetaData 列的元数据，从DatabaseMetaData.getColumns得到
     * @return 列是否可为空。若无法判断，则返回true
     */
    protected boolean isColumnNullable(ResultSet columnMetaData) {
        try {
            // ISO规则用来确定某一列的为空性。 YES---如果该参数可以包括空值; NO---如果参数不能包含空值
            // 空字符串---如果参数为空性是未知的
            String sNullable = columnMetaData.getString(IS_NULLABLE);
            return "YES".equals(sNullable);
        } catch (SQLException e) {
            log.warn("【元数据获取】获取列nullable失败", e);
        }
        return true;
    }


    /**
     * 判断该列是否自增
     *
     * @param columnMetaData 列的元数据，从DatabaseMetaData.getColumns得到
     * @return 列是否自增。若无法判断，则返回false
     */
    protected boolean isColumnAutoincrement(ResultSet columnMetaData) {
        try {
            //  指示此列是否是自动递增 是---如果该列是自动递增 无---如果不是自动递增列 空字串---如果不能确定它是否
            //  列是自动递增的参数是未知
            String sAutoincrement = columnMetaData.getString(IS_AUTOINCREMENT);
            return "YES".equals(sAutoincrement);
        } catch (SQLException e) {
            log.warn("【元数据获取】获取列autoincrement失败", e);
        }
        return false;
    }
}
